Backup SQL Dump with Bash

Table of Contents

Background #

For years, we have had the idea of a kiosk terminal for our clubhouse, which members and guests could use to buy drinks, guest lessons and tennis accessories directly via an EC terminal or an invoice function. The project has now been implemented and put into operation. A web server with the application and a local database was created on our Igel client.

The idea #

This database must now be backed up in case it is no longer accessible due to a failure or defect in the hedgehog. I wanted to have a simple, relatively uncomplicated solution that can be used on several Linux systems without major dependencies; so it should be Bash.

The script should do the following:

Create SQL dump for MySQL and PostgreSQL
Send SQL dump to the backup server and check for integrity to prevent data loss

I need three tools for this:

mysqldump and pg_dump
rsync (because integrity check after transfer)

Code #

The backup script consists of three files:

backup-config.sh
backup-rsa.priv
backup.sh

The backup script is customized and controlled via backup-config.sh. All configurations take place exclusively here.

#!/bin/sh

# example: helpdesk
BACKUPFILENAME=dvdrental

# example: /var/backups/helpdesk/
BACKUPPATH=/tmp/

# example: helpdesk_db
BACKUPDBNAME=dvdrental

# example: backupserver.example.com
BACKUPSERVER=backupserver.example.com

# example: home/rbackup/backups/
BACKUPSERVERPATH=/home/rbackup/backups/

# usable: 'pgsql' or 'mysql'
DATABASETYPE=pgsql

# example: admin
DATABASEUSER=postgres

# example: jksdhfHFkhas8324
DATABASEPASS=fasdjnfkjn(/hnfsdaljf

export BACKUPNAME
export BACKUPPATH
export BACKUPDBNAME
export BACKUPSERVER
export BACKUPSERVERPATH
export DATABSETYPE
export DATABASEUSER
export DATABASEPASS

The file backup-rsa.priv contains the private key to connect to the backup server with the defined user via SSH.

The actual backup script is executed with the file backup.sh.

#!/bin/sh

# load config
if [ ! -e /root/backup-config.sh ]; then
        echo no backup-config
        logger backup unconfigured
        exit 1
fi

if [ ! -e /root/backup-rsa.priv ]; then
        echo no backup-rsa key
        logger backup-rsa key unconfigured
        exit 1
fi

. /root/backup-config.sh

set -f

# validation
ERROR=()
if [ "x$BACKUPFILENAME" = "x" ]; then
        ERROR+=("Backup Error: Backup filename incorrectly!")
fi

if [ "x$BACKUPPATH" = "x" ]; then
	ERROR+=("Backup Error: Backup path incorrectly!")
fi

if [ "x$BACKUPDBNAME" = "x" ]; then
        ERROR+=("Backup Error: Backup database name incorrectly!")
fi

if [ "x$DATABASETYPE" = "x" ]; then
        ERROR+=("Backup Error: Database type incorrectly!")
else
	if [ "$DATABASETYPE" = "pgsql" ]; then
		DATABASECMD=pg_dump
		if ! [ -x "$(command -v $DATABASECMD)" ]; then
		        ERROR+=("Backup Error: $DATABASECMD is not available!")
		fi

	elif [ "$DATABASETYPE" = "mysql" ]; then
                DATABASECMD=mysqldump
                if ! [ -x "$(command -v $DATABASECMD)" ]; then
                        ERROR+=("Backup Error: $DATABASECMD is not available!")
                fi
	else
		ERROR+=("Backup Error: Database type invalid!")
	fi
fi

if [ "x$DATABASEUSER" = "x" ]; then
        ERROR+=("Backup Error: Database user incorrectly!")
fi

if [ "x$DATABASEPASS" = "x" ]; then
        ERROR+=("Backup Error: Database password incorrectly!")
fi

if [ "x$BACKUPSERVER" = "x" ]; then
        ERROR+=("Backup Error: Backup server incorrectly!")
fi

if [ "x$BACKUPSERVERPATH" = "x" ]; then
        ERROR+=("Backup Error: Backup server path incorrectly!")
fi

if ! [ -x "$(command -v rsync)" ]; then
	ERROR+=("Backup Error: rsync is not available!")
fi

if ! [ -d "$BACKUPPATH" ]; then
	ERROR+=("Backup Error: Backup directory $BACKUPPATH does not exist!")
fi

if ! : >/dev/tcp/8.8.8.8/53; then
	ERROR+=("Backup Error: Internet not available!")
fi

if ! [ ${#ERROR[@]} -eq 0 ]; then
	printf "%s\n" "${ERROR[@]}"
	for ERR in ${!ERROR[@]}; do
		logger -t rbackup -p user.info "${ERROR[$ERR]}"
	done
        exit 1
fi

TODAY=`date +%Y-%m-%d`
# start backup
STARTTIME=`date +%s%3N`
if [ "$DATABASETYPE" = "pgsql" ]; then
	PGPASSWORD=$DATABASEPASS $DATABASECMD -h 127.0.0.1 -U $DATABASEUSER $BACKUPDBNAME | gzip > $BACKUPPATH$BACKUPFILENAME-$TODAY.sql.gz 2>&1|wc --bytes >/tmp/size && CREATERESULT=1 || CREATERESULT=0
elif [ "$DATABASETYPE" = "mysql" ]; then
        $DATABASECMD -u $DATABASEUSER --password=$DATABASEPASS $BACKUPDBNAME | gzip > $BACKUPPATH$BACKUPFILENAME-$TODAY.sql.gz 2>&1 CREATERESULT=1 || CREATERESULT=0
fi
ENDTIME=`date +%s%3N`

#echo $CREATERESULT
if [ $CREATERESULT -eq 0 ]; then
        echo "backup failed to create (needed $(($ENDTIME - $STARTTIME))ns)"
	logger -t rbackup -p user.info "backup failed to create (needed $(($ENDTIME - $STARTTIME))ns)"
else
	FILESIZE=$(wc -c $BACKUPPATH$BACKUPFILENAME-$TODAY.sql.gz | cut -d' ' -f1)
        echo "backup successfully created (needed $(($ENDTIME - $STARTTIME))ns / Backup file size: $FILESIZE bytes)"
	logger -t rbackup -p user.info "backup successfully created (needed $(($ENDTIME - $STARTTIME))ns / Backup file size: $FILESIZE bytes)"
fi


# sync backup
STARTTIME=`date +%s%3N`
nice rsync -a -W --stats --timeout 300 -e "ssh -o StrictHostKeyChecking=no -l rbackup -i backup-rsa.priv" $BACKUPPATH$BACKUPFILENAME-$TODAY.sql.gz $BACKUPSERVER:$BACKUPSERVERPATH 2>&1|grep "Total transferred file size" >/tmp/transfered
ENDTIME=`date +%s%3N`

SENDRESULT=$?
TRANSFERED=`cat /tmp/transfered`
#echo $SENDRESULT
if [ "x$TRANSFERED" = "x" ]; then
        echo "backup failed to sent (needed $(($ENDTIME - $STARTTIME))ns / $TRANSFERED)"
	logger -t rbackup -p user.info "backup failed to sent (needed $(($ENDTIME - $STARTTIME))ns / $TRANSFERED)"
else
        echo "backup successfully sent (needed $(($ENDTIME - $STARTTIME))ns / $TRANSFERED)"
	logger -t rbackup -p user.info "backup successfully sent (needed $(($ENDTIME - $STARTTIME))ns / $TRANSFERED)"
fi

How it works #

Before the backup.sh script does anything at all, it first runs through a simple validation, which checks the following:

whether backup-config.sh exists
whether backup-rsa.priv exists
all parameters in backup-config.sh are specified
whether rsync is installed
whether pg_dump oder mysqldump is installed
whether a internet connection vorhanden ist

If all this is true, the script creates a dump of the specified database
It then attempts to send the dump to the backup server and store it in the specified directory.

[ed-box color=“info”] Do not forget to make the script executable with chmod +x backup.sh and also to restrict the private key with chmod 600 backup-rsa.priv, otherwise it will not be activated by the SSH daemon. [/ed-box]

Logging is done via logger directly into the syslog on the server.

At the end we will see a success or fail message:

and our dump should then have arrived on the backup server with a success message:

Done.

Cheers mate,